﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using VisaCRM.Modules.Admin.Model;
using VisaCRM.Common.Helper;

namespace VisaCRM.Modules.Admin.Gateway
{
    class ConsultantGateway
    {
        internal System.Collections.ObjectModel.ObservableCollection<Consultant> GetAllConsultants()
        {
            ObservableCollection<Consultant> allConsultant = new ObservableCollection<Consultant>();
            DataTable dt = new DataTable();
            dt = DBHelper.GetDataTable(QueryProvider.MakeSQLQueryToFetchConsultantList());
            if (dt.Rows.Count <= 0)
            {
                return allConsultant;
            }
            foreach (DataRow dataRow in (InternalDataCollectionBase)dt.Rows)
            {
                allConsultant.Add(new Consultant()
                {
                    ConsultantID = Convert.ToInt32(dataRow["Con_ID"].ToString()),
                    ConsultantName = dataRow["Con_Name"].ToString(),
                    ConsultantJobTitle = dataRow["Con_JobTitle"].ToString(),
                    ConsultantStatus = Convert.ToInt32(dataRow["Con_Status"].ToString()),
                    ConsultantDepartment = Convert.ToInt32(dataRow["Con_Department"].ToString()),
                    ConsultantBranchID = Convert.ToInt32(dataRow["Con_BranchID"].ToString()),
                    ConsultantDDI = dataRow["Con_DDI"].ToString(),
                    ConsultantDDIUK = dataRow["Con_DDIUK"].ToString(),
                    ConsultantEmail = dataRow["Con_Email"].ToString(),
                    ConsultantSupervisorID = Convert.ToInt32(dataRow["Con_SupervisorID"].ToString()),
                    ConsultantAccreditation = dataRow["Con_Accreditation"].ToString(),
                    ConsultantSecurityLevel = Convert.ToInt32(dataRow["Con_SecurityLevel"].ToString()),

                });
            }
            return allConsultant;
        }

        internal Consultant GetCurrentSupervisor(int id)
        {
            Consultant supervisor = new Consultant();
            DataTable dt = new DataTable();
            dt = DBHelper.GetDataTable(QueryProvider.MakeSQLQueryToFetchCurrentSupervisor(id));
            if (dt.Rows.Count <= 0)
            {
                return supervisor;
            }
            foreach (DataRow dataRow in (InternalDataCollectionBase)dt.Rows)
            {
                supervisor.ConsultantID = Convert.ToInt32(dataRow["Con_ID"].ToString());
                supervisor.ConsultantName = dataRow["Con_Name"].ToString();
            }
            return supervisor;
        }

        internal ObservableCollection<Consultant> GetAllSupervisor()
        {
            ObservableCollection<Consultant> allSupervisor = new ObservableCollection<Consultant>();
            DataTable dt = new DataTable();
            dt = DBHelper.GetDataTable(QueryProvider.MakeSQLQueryToFetchSupervisorList());
            if (dt.Rows.Count <= 0)
            {
                return allSupervisor;
            }
            foreach (DataRow dataRow in (InternalDataCollectionBase)dt.Rows)
            {
                allSupervisor.Add(new Consultant()
                {
                    ConsultantID = Convert.ToInt32(dataRow["Con_ID"].ToString()),
                    ConsultantName = dataRow["Con_Name"].ToString(),
                });
            }
            return allSupervisor;
        }

        internal ObservableCollection<string> GetAllStatus()
        {
            ObservableCollection<string> allStatus = new ObservableCollection<string>();
            allStatus.Add("Active");
            allStatus.Add("Inactive");
            return allStatus;
        }

        internal string GetCurrentConsultantStatus(int p)
        {
            string status = string.Empty;
            if (p == 0)
                status = "Inactive";
            else
                status = "Active";
            return status;
        }

        internal void DeleteConsultantFromDatabase(int rowid)
        {
            string query = QueryProvider.MakeSQLQueryToDeleteConsultantFromDatabase(rowid);
            DBHelper.ExecuteSqlQuery(query);
        }

        internal void EditConsultantToDatabase(int rowid, string ConName, int conbranchId, string conAccredionDetail, int condep, int consupid, string conEmail, string conddi, string conddiUk, string conjobTit, int consecLevel, int constatus)
        {
            string query = QueryProvider.MakeSQLQueryToEditConsultantToDatabase(rowid, ConName, conbranchId, conAccredionDetail, condep, consupid, conEmail, conddi, conddiUk, conjobTit, consecLevel, constatus);
            DBHelper.ExecuteSqlQuery(query);
        }

        internal void SaveNewConsultantToDatabase(string ConName, int conbranchId, string conAccredionDetail, int condep, int consupid, string conEmail, string conddi, string conddiUk, string conjobTit, int consecLevel, int constatus)
        {
            string query = QueryProvider.MakeSQLQueryToInsertConsultantToDatabase(ConName, conbranchId, conAccredionDetail, condep, consupid, conEmail, conddi, conddiUk, conjobTit, consecLevel, constatus);
            DBHelper.ExecuteSqlQuery(query);
        }
    }
}
